package org.bmp.zemail.server.common.services;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.bmp.zemail.cache.services.ICacheService;
import org.bmp.zemail.login.user.models.User;
import org.bmp.zemail.server.common.models.Server;
import org.bmp.zemail.server.credential.models.Credentials;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.stereotype.Service;
import javax.sql.DataSource;

@Service
public class ServerDao {
	
	private static final StringBuffer SQL_SERVERS_BY_USERNAME = new StringBuffer("SELECT s.id, s.name, s.type, ").
			append("s.protocol, s.url, c.username, c.password FROM servers s, credentials c ").
			append("WHERE s.credential_id = c.id ").
			append("AND s.users_username = ?");
	
	private static final StringBuffer SQL_SERVER_BY_ID = new StringBuffer("SELECT s.id, s.name, s.type, ").
			append("s.protocol, s.url, s.users_username, c.username, c.password FROM servers s, credentials c ").
			append("WHERE s.credential_id = c.id ").
			append("AND s.id = ?");
	
	/*
	 * Spring injection
	 */
	@Autowired private DataSource dataSource;
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	@Autowired private ICacheService iCacheService;
	public void setCacheService(ICacheService iCacheService) {
		this.iCacheService = iCacheService;
	}

	public Server getServerById(String serverId) {
		Server server = iCacheService.getServerList().get(serverId);
		if (server == null) {
			server = getServerByIdFromDB(serverId);
		}
		return server;
	}
	
	private Server getServerByIdFromDB(String serverId) {
		final DefaultLobHandler lobHandler = new DefaultLobHandler();
		JdbcTemplate jdbc = new JdbcTemplate(this.dataSource);
		
		Server server = (Server) jdbc.query(SQL_SERVER_BY_ID.toString(), new Object[] {serverId}, 
				new RowMapper<Server>() {
					@Override
					public Server mapRow(ResultSet rs, int col)
							throws SQLException {
						Credentials credentials = new Credentials();
						credentials.setUsername(rs.getString("c.username"));
						credentials.setEncryptedPassword(lobHandler.getBlobAsBytes(rs, "password"));
						
						User user = new User();
						user.setUsername(rs.getString("s.users_username"));
						
						Server server = new Server();
						server.setId(rs.getString("s.id"));
						server.setUser(user);
						server.setCredentials(credentials);
						server.setName(rs.getString("s.name"));
						server.setProtocol(rs.getString("s.protocol"));
						server.setUrl(rs.getString("s.url"));
						server.setType(rs.getString("s.type"));
						
						return server;
					}
	    		});
		//add server to list
		iCacheService.updateServerListCache(serverId, server);
		
		return server;
	}
	
	public List<Server> getServersByUsername(final String username) {
		final DefaultLobHandler lobHandler = new DefaultLobHandler();
		JdbcTemplate jdbc = new JdbcTemplate(this.dataSource);
		
		List<Server> servers = jdbc.query(SQL_SERVERS_BY_USERNAME.toString(), new Object[] {username}, 
				new RowMapper<Server>() {
					@Override
					public Server mapRow(ResultSet rs, int col)
							throws SQLException {
						Credentials credentials = new Credentials();
						credentials.setUsername(rs.getString("c.username"));
						credentials.setEncryptedPassword(lobHandler.getBlobAsBytes(rs, "password"));
						
						User user = new User();
						user.setUsername(username);
						
						Server server = new Server();
						server.setId(rs.getString("s.id"));
						server.setUser(user);
						server.setCredentials(credentials);
						server.setName(rs.getString("s.name"));
						server.setProtocol(rs.getString("s.protocol"));
						server.setUrl(rs.getString("s.url"));
						server.setType(rs.getString("s.type"));
						
						return server;
					}
	    		});
		
		//insert all servers in the map
		for (Server server : servers) {
			iCacheService.updateServerListCache(server.getId(), server);
		}
		
		return servers;
	}
}
